package com.jiuyun.dao.impi;

import com.jiuyun.dao.WorkDao;
import com.jiuyun.entity.ClassName;
import com.jiuyun.entity.Kqcx;
import com.jiuyun.entity.Work;
import com.jiuyun.util.AliPool;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class WorkDaoImpl implements WorkDao {



    //添加记录
    @Override
    public int addrecord(int stuid, String cwork, String ctime, String crema) throws SQLException {
        QueryRunner queryRunner=new QueryRunner();
        String sql="INSERT INTO homework VALUES(NULL,?,?,?,?)";
        Connection   conn= AliPool.getConn();
        int ok=queryRunner.update(conn,sql,stuid,cwork,ctime,crema);
        return ok;
    }

    @Override
    public List workfind(String b, String t) throws SQLException {
        QueryRunner queryRunner=new QueryRunner();
        String sql="select s.stname,h.`work`,h.ctime,h.remark,day(h.ctime) from classes c INNER JOIN student s on s.classId=c.classId INNER JOIN homework h ON h.studentId=s.stid WHERE className=? AND month(h.ctime)=?;";
        Connection connection=AliPool.getConn();
        List list=queryRunner.query(connection,sql,new BeanListHandler<Work>(Work.class),b,t);
        connection.close();
        return list;
    }
    @Override
    public List<ClassName> findcs() throws SQLException {
        String sql="select className from classes";
        Connection conn=AliPool.getConn();
        List list=new ArrayList();
        PreparedStatement pst=conn.prepareStatement(sql);
        ResultSet rs=pst.executeQuery();
        while(rs.next()){
            String cname=rs.getString(1);
            ClassName stu=new ClassName(cname);
            list.add(stu);
        }
        return list;
    }
    @Override
    public List<Kqcx> finwo(String b, String t) throws SQLException {
        String sql="select s.stname,h.`work`,h.ctime,h.remark,day(h.ctime) from classes c INNER JOIN student s on s.classId=c.classId INNER JOIN homework h ON h.studentId=s.stid WHERE className=? AND month(h.ctime)=?;";
        Connection conn=AliPool.getConn();
        List<Kqcx> list=new ArrayList<Kqcx>();
        PreparedStatement pst=conn.prepareStatement(sql);
        pst.setString(1,b);
        pst.setString(2,t);
        ResultSet rs=pst.executeQuery();
        while(rs.next()){
            String stname=rs.getString(1);
            String work=rs.getString(2);
            Timestamp ctime=rs.getTimestamp(3);
            String rema=rs.getString(4);
            String cday=rs.getString(5);
            Kqcx kq=new Kqcx(stname, work,ctime,rema, cday);
            list.add(kq);
        }
        conn.close();
        return list;
    }
    @Override
    public List<Work> findseex(String examid) throws SQLException {
        String sql="SELECT exam_id,exam_name,exam_start_date,exam_end_date FROM exam WHERE exam_id=?";
        Connection conn=AliPool.getConn();
        List list=new ArrayList();
        PreparedStatement pst=conn.prepareStatement(sql);
        pst.setString(1,examid);
        ResultSet rs=pst.executeQuery();
        while(rs.next()){
            String ename=rs.getString(2);
            String estime=rs.getString(3);
            String eetime=rs.getString(4);
            String eid=rs.getString(1);
            Work work=new Work(ename,estime,eetime,eid);
            list.add(work);
        }
    return  list;
    }
}
